package com.zkyc.framework.common.mapper;

import com.alibaba.fastjson.JSONObject;
import com.zkyc.framework.common.pojo.Pi;
import com.zkyc.framework.common.pojo.WebClickRate;
import org.apache.ibatis.annotations.Mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * (WebClickRate)表数据库访问层
 *
 * @author libingchuan
 * @since 2022-06-08 15:49:46
 */
@Mapper
public interface WebClickRateMapper extends BaseMapper<WebClickRate>{

    @Select("SELECT avg(click_rate) from system_web_click_rate where website_id = #{websiteId} " +
            "and date_format(click_date,'%Y-%m-%d') < date_format(now(),'%Y-%m-%d')")
    String getAvgClickRate(Integer websiteId);

    @Select("select id from web_website  where is_deleted = 0;")
    List<Integer> getAllWebSiteId();

    @Select("SELECT click_rate from system_web_click_rate where website_id = #{websiteId} " +
            "and date_format(click_date,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')")
    String getTodayClickRate(Integer websiteId);

    @Select("SELECT click_date,click_rate from system_web_click_rate where website_id = #{websiteId} and date_format(click_date,'%Y-%m-%d') " +
            "< date_format(now(),'%Y-%m-%d') and date_format(click_date,'%Y-%m-%d') >= date_format(date_sub(now()," +
            " interval 7 day),'%Y-%m-%d') order BY click_date asc limit 7 ")
    List<JSONObject> getSevenDaysClickRate(Integer websiteId);
    @Select("SELECT avg(cost_time) as cost_time,date_format(request_date,'%Y-%m-%d') as request_date from system_timing " +
            "where website_id = #{websiteId} and date_format(request_date,'%Y-%m-%d') < date_format(now(),'%Y-%m-%d') and date_format(request_date,'%Y-%m-%d') " +
            ">= date_format(date_sub(now(), interval 7 day),'%Y-%m-%d') group by date_format(request_date,'%Y-%m-%d') ORDER BY request_date")
    List<JSONObject> getAvgCostTime(Integer websiteId);
}

